set echo off
--------------------------------------------------------------------------------
-- @name: prof_latch2
-- @author: dion cho
-- @note: profile latch2 - extended version of prof_latch
-- @usage: 
--    @prof_latch2 10 151 % "name,called"
--        :profile latch activity for sid 151 and latch name % with columns "name,called"
--        , for 10 sec
--------------------------------------------------------------------------------

define __INTERVAL = &1
define __SID = "&2"
define __LATCHNAME = "&3"
define __COLUMNS = "&4"

col sid format 999
col called format a10
col name format a25
col object format a17
col held_time head "held|time(ms)" format 9999999.99
col held_pct head "held(%)" format 99
col avg_held_time head "avg held|time(ms)" format 9999999.99


col start_time new_value start_time
select dbms_utility.get_time as start_time from dual;

select
  sid, 
  &__COLUMNS,
  trunc(100*(latch_hits/(max(total_hits) over())),1) as held_pct,
  trunc(1000*&__INTERVAL * (latch_hits/(max(total_hits) over())),2) as held_time, 
  trunc(1000*&__INTERVAL * * ((latch_hits/dist_hits)/(max(total_hits) over())),2) as avg_held_time 
from
  (
    select /*+ ordered use_nl(l) */
      l.sid,
      &__COLUMNS,
      count(*) as latch_hits,
      count(distinct gets) as dist_hits,
      max(lvl) as total_hits
    from 
      (select /*+ no_merge */ level as lvl from dual connect by level <= 10000000000 
          and (dbms_utility.get_time - &start_time) <= &__INTERVAL * 100) t1,
      (select /*+ no_merge */ 
          ksuprsid as sid, 
          ksuprlat as laddr,
          ksulagts as gets,
          (select ksllwnam from sys.xm$ksllw where indx = ksulawhr) as called,
          ksuprlnm as name,
          TO_CHAR(ksulawhy,'XXXXXXXXXXXXXXXX') object
      from sys.xm$ksuprlat
      where ksuprsid in (&__SID) and ksuprlnm like '%&__LATCHNAME%') l
    group by
      l.sid, &__COLUMNS
    order by 
      count(*) desc
  )
;

set echo on
